package com.glwan.fixedassetapp.common;

import java.util.List;

public class DatabaseConstant {

    /**
     * 数据库名称
     */
    public static final String DATABASE_NAME = "glwan_faa.db";

    /**
     * 重置sqlite的自增id
     */
    public static final String RESET_SQLITE_SEQUENCE = "DELETE FROM sqlite_sequence;";

    //----------------------------离线盘点版本的SQL----------------------------

    public static final String BASIC_PRODUCT_INFO = "" +
            "CREATE TABLE BASIC_PRODUCT_INFO (\n" +
            "\tID INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
            "\tDEPART VARCHAR ( 20 ),\n" +
            "\tITEM_NUMBER INTEGER,\n" +
            "\tCATEGORY VARCHAR ( 20 ),\n" +
            "\tPRODUCT_CODE VARCHAR ( 20 ),\n" +
            "\tSUB_CODE VARCHAR ( 4 ),\n" +
            "\tPRODUCT_NAME VARCHAR ( 20 ),\n" +
            "\tSPEC VARCHAR ( 20 ),\n" +
            "\tSTORAGE_AREA VARCHAR ( 20 ),\n" +
            "\tSTORAGE_LOCATION VARCHAR ( 20 ),\n" +
            "\tQUANTITY VARCHAR ( 2 ),\n" +
            "\tCOST_CENTER VARCHAR ( 20 ),\n" +
            "\tCUSTODIAN_COST_CENTER VARCHAR ( 20 ),\n" +
            "\tCUSTODIAN VARCHAR ( 20 ),\n" +
            "\tCONTROL_STATUS VARCHAR ( 4 ),\n" +
            "\tASSET_STATUS VARCHAR ( 20 ),\n" +
            "\tASSET_TIME VARCHAR ( 20 ),\n" +
            "\tREMARK VARCHAR ( 200 ),\n" +
            "INVENTORY_STATUS VARCHAR ( 4 ) \n" +
            ");";

    public static final String INVENTORY_RECORD = "CREATE TABLE INVENTORY_RECORD ( \n" +
            "ID INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
            "PRODUCT_CODE VARCHAR ( 20 ), \n" +
            "INVENTORY_STATUS VARCHAR ( 20 ), \n" +
            "INVENTORY_TIME DATETIME, INVENTORY_LOCATION VARCHAR ( 20 ) );";

    public static final String INVENTORY_RECORD_PRODUCT_CODE_INDEX = "CREATE INDEX PRODUCT_CODE_INDEX ON INVENTORY_RECORD (PRODUCT_CODE);";
    public static final String INVENTORY_RECORD_INVENTORY_STATUS_INDEX = "CREATE INDEX INVENTORY_STATUS_INDEX ON INVENTORY_RECORD (INVENTORY_STATUS);";
    public static final String INVENTORY_RECORD_INVENTORY_LOCATION_INDEX = "CREATE INDEX INVENTORY_LOCATION_INDEX ON INVENTORY_RECORD (INVENTORY_LOCATION);";
    public static final String INVENTORY_RECORD_INVENTORY_TIME_INDEX = "CREATE INDEX INVENTORY_TIME_INDEX ON INVENTORY_RECORD (INVENTORY_TIME);";


    //--------------------------------------------发改委在线版--------------------------------------------
    public static final String INVENTORY_TASK = "INVENTORY_TASK";
    public static final String INVENTORY_TASK_SQL =
            "CREATE TABLE " + INVENTORY_TASK + " (\n" +
                    "  \"ID\" TEXT(32) NOT NULL,\n" +
                    "  \"DOC_NUMBER\" TEXT(100),\n" +
                    "  \"DOC_CREATE_TIME\" TEXT(20),\n" +
                    "  \"DOC_SUBJECT\" TEXT(100),\n" +
                    "  \"FD_TASK_TIME\" TEXT(20),\n" +
                    "  \"FD_UNIT\" TEXT(20),\n" +
                    "  \"FD_ASSIGN_PERSON\" TEXT(20),\n" +
                    "  PRIMARY KEY (\"ID\")\n" +
                    ");";

    public static final String INVENTORY_TASK_ITEM = "INVENTORY_TASK_ITEM";
    public static final String INVENTORY_TASK_ITEM_SQL =
            "CREATE TABLE " + INVENTORY_TASK_ITEM + " (\n" +
                    "  \"FD_PD_ID\" TEXT(32) NOT NULL,\n" +
                    "  \"FD_TASK_ID\" TEXT(32),\n" +
                    "  \"FD_YZ_CARD_NAME\" TEXT(100),\n" +
                    "  \"FD_YZ_CARD_ID\" TEXT(100),\n" +
                    "  \"FD_YZ_CARD_CODE\" TEXT(50),\n" +
                    "  \"FD_SIZE\" TEXT(100),\n" +
                    "  \"FD_LOCATION\" TEXT(100),\n" +
                    "  \"FD_CARD_CATEGORY_NAME\" TEXT(100),\n" +
                    "  \"FD_DUTY_DEPT_NAME\" TEXT(100),\n" +
                    "  \"FD_DUTY_PERSON_NAME\" TEXT(20),\n" +
                    "  \"REASON\" TEXT(100),\n" +
                    "  \"STATUS\" INTEGER(2) DEFAULT 0,\n" +
                    "  PRIMARY KEY (\"FD_PD_ID\")\n" +
                    ");";

    //--------------------------------------------发改委离线版--------------------------------------------
    public static final String OFFLINE_INVENTORY_TASK_ITEM = "OFFLINE_INVENTORY_TASK_ITEM";

    public static String generateCreateTableSQL() {
        List<String> key = ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE_COLUMN_ARRAY_NEW;
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("CREATE TABLE ").append(OFFLINE_INVENTORY_TASK_ITEM).append(" (\n");

        for (String columnName : key) {
            sqlBuilder.append("  \"").append(columnName).append("\" TEXT,\n");
        }

        sqlBuilder.append("  PRIMARY KEY (\"").append(key.get(0)).append("\")\n")
                .append(");");

        return sqlBuilder.toString();
    }

}
